* Set current directory and open log
cd "E:\Seagate Sync\VOL\Personal folder\Documents\Research\Current projects\Working party meetings\Posted\EUP 160404"
capture log close
log using "Data management\wpar-management03-coreper", replace text


***********************************************************************************
* Coding of Coreper meetings and generation of meeting level and aggregate datasets
***********************************************************************************


* Project and author information
********************************
* Programme:	wpar-management03-coreper.do
* Date:			4 April 2016
* Author:		Frank Haege, University of Limerick
* Contact:		frank.haege@ul.ie 

* Do-file description
*********************
* Relying on the annual meeting calendars supplied by the Council Secretariat in response
* to a request for access to documents, this do-file generates a dataset with information 
* on the dates and duration (in half-days) of Coreper meetings between 1996 and 2014.


* Clear memory  
**************
version 12.0
clear all
macro drop _all
set more off

* Load and append dataset with information on dates and duration of working party meetings
use "Input data\wpar-rawdata-coreper-1996-2014.dta", clear

* Describe data set
describe, d

* Rename and label committee name variables
rename meetingdescription description
label var description "Meeting description"
* Remove leading and trailing blanks
replace description = trim(description)
* Collapse multiple internal blanks
replace description = itrim(description)

* Generate date of meeting variable
generate date = date(sessiondate, "DMY")
format date %d
label var date "Meeting date"
tab date, m

* Generate categorical time-slot variable
generate slot = .
replace slot = 1 if slots == "AM"
replace slot = 2 if slots == "PM"
replace slot = 3 if slots == "EV"
replace slot = 4 if slots == "AM-PM"
replace slot = 5 if slots == "PM-EV"
replace slot = 6 if slots == "AM-EV"
replace slot = 7 if slots == "AM-PM-EV"
label var slot "Meeting time slot"
label def slotl 1 "AM" 2 "PM" 3 "EV" 4 "AM-PM" 5 "PM-EV" 6 "AM-EV" 7 "AM-PM-EV"
label val slot slotl
tab slots, m
tab slot, m

* Drop variables that are not needed
keep date slot description
order date slot description

* Generate committee name variable
generate cname = .
replace cname = 1 if regexm(description, "COREPER 1")
replace cname = 2 if regexm(description, "COREPER 2")

* Label committee name variable and variable values
label var cname "Committee name"
label def cnamel 1 "Coreper 1" 2 "Coreper 2"
label val cname cnamel
tab cname, m

* Check observations without a match
list description date if cname == .
replace cname = 2 if description == "CTE. MIXTE"

* Drop remaining observations
drop if cname == .
* 18 observations dropped

* Generate meeting number variable
generate cnum = .
replace cnum = real(regexs(0)) if (regexm(description, "[0-9][0-9][0-9][0-9]"))
label var cnum "Meeting number"
tab cnum, m

* Correct meeting number variable
list date slot cname description cnum if cnum < 1681 | (cnum > 2560 & cnum != .)
replace cnum = 1834 if cnum == 1234
replace cnum = 1954 if cnum == 1054
replace cnum = 2367 if cnum == 3267
replace cnum = . if cnum == 4755
list date slot cname description cnum if cnum == 2000
list date slot cname description cnum if regexm(description, "[Aa][Gg][Ee][Nn][Dd][Aa] 2000")
replace cnum = . if regexm(description, "[Aa][Gg][Ee][Nn][Dd][Aa] 2000")

* Identify and drop duplicates
sort cname date -cnum
duplicates report date slot cname
duplicates tag date slot cname, gen(duplicate)
list date slot cname description cnum if duplicate == 1
duplicates drop date slot cname, force
* 18 observations dropped
drop duplicate

* Generate time slot dummy variables
generate am = 0
replace am = 1 if slot == 1 | slot == 4 | slot == 6 | slot == 7
label var am "Morning (yes/no)"
generate pm = 0
replace pm = 1 if slot == 2 | slot == 4 | slot == 5 | slot == 7
label var pm "Afternoon (yes/no)"
generate ev = 0
replace ev = 1 if slot == 3 | slot == 5 | slot == 6 | slot == 7
label var ev "Evening (yes/no)"
label def yesno 0 "No" 1 "Yes"
label val am yesno
label val pm yesno
label val ev yesno
tab slot am, m
tab slot pm, m
tab slot ev, m

* Identify and drop duplicates
gsort date cname -slot
duplicates report date cname am
duplicates list date cname am
duplicates tag date cname am, gen(duplicate)
list date slot cname description cnum am if duplicate == 1
duplicates drop date cname am, force
* 8 observations dropped
drop duplicate

duplicates report date cname pm
duplicates list date cname pm
duplicates tag date cname pm, gen(duplicate)
list date slot cname description cnum pm if duplicate == 1
replace cnum = 1900 if description == "EQUIPE DE NUIT COREPER 1" & date == d(11-12-2000)
duplicates drop date cname pm, force
* 13 observations dropped
drop duplicate

* Collapse observations if they have meetings on the same day
sort date cname cnum
duplicates report date cname ev
duplicates list date cname ev
duplicates tag date cname ev, gen(duplicate)
list date slot cname description cnum ev am pm if duplicate == 1
replace slot = 4 if duplicate == 1
replace am = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
duplicates drop date cname ev, force
* 43 observations dropped
drop duplicate

duplicates report date cname
duplicates list date cname
duplicates tag date cname, gen(duplicate)
list date slot cname description cnum ev am pm if duplicate == 1
replace slot = 7 if duplicate == 1
replace am = 1 if duplicate == 1
replace pm = 1 if duplicate == 1
replace ev = 1 if duplicate == 1
duplicates drop date cname, force
* 4 observations dropped
drop duplicate

* Generate meeting time variable
generate dur = .
replace dur = am + pm + ev
label var dur "Meeting duration"
tab dur, m
tab slot dur, m

* Generate year variable
generate year = year(date)
label var year "Meeting year"
by cname, sort: tab year

* Generate month variable
generate month = month(date)
label var month "Meeting month"
label def monthl 1 "January" 2 "February" 3 "March" 4 "April" 5 "May" 6 "June" /*
	*/ 7 "July" 8 "August" 9 "September" 10 "October" 11 "November" 12 "December"
label val month monthl
by cname, sort: tab month
by year cname, sort: tab month

* Generate week of year variable
generate week = week(date)
label var week "Meeting week"
by cname, sort: tab week, m

* Generate day of week variable
generate day = dow(date)
label var day "Meeting day of week"
label define dayl 0 "Sunday" 1 "Monday" 2 "Tuesday" 3 "Wednesday" /*
	*/ 4 "Thursday" 5 "Friday" 6 "Saturday"
label val day dayl
by cname, sort: tab day, m

* Generate semester variable
generate semester = halfyear(date)
label var semester "Meeting semester"
label def semesterl 1 "First semester" 2 "Second semester"
label val semester semesterl
tab semester, m
by cname, sort: tab semester, m

* Generate presidency variable
generate pres = .
replace pres = 1 if year == 1995 & month <= 6
replace pres = 2 if year == 1995 & month > 6
replace pres = 3 if year == 1996 & month <= 6
replace pres = 4 if year == 1996 & month > 6
replace pres = 5 if year == 1997 & month <= 6
replace pres = 6 if year == 1997 & month > 6
replace pres = 7 if year == 1998 & month <= 6
replace pres = 8 if year == 1998 & month > 6
replace pres = 9 if year == 1999 & month <= 6
replace pres = 10 if year == 1999 & month > 6
replace pres = 11 if year == 2000 & month <= 6
replace pres = 12 if year == 2000 & month > 6
replace pres = 13 if year == 2001 & month <= 6
replace pres = 14 if year == 2001 & month > 6
replace pres = 15 if year == 2002 & month <= 6
replace pres = 16 if year == 2002 & month > 6
replace pres = 17 if year == 2003 & month <= 6
replace pres = 18 if year == 2003 & month > 6
replace pres = 19 if year == 2004 & month <= 6
replace pres = 20 if year == 2004 & month > 6
replace pres = 21 if year == 2005 & month <= 6
replace pres = 22 if year == 2005 & month > 6
replace pres = 23 if year == 2006 & month <= 6
replace pres = 24 if year == 2006 & month > 6
replace pres = 25 if year == 2007 & month <= 6
replace pres = 26 if year == 2007 & month > 6
replace pres = 27 if year == 2008 & month <= 6
replace pres = 28 if year == 2008 & month > 6
replace pres = 29 if year == 2009 & month <= 6
replace pres = 30 if year == 2009 & month > 6
replace pres = 31 if year == 2010 & month <= 6
replace pres = 32 if year == 2010 & month > 6
replace pres = 33 if year == 2011 & month <= 6
replace pres = 34 if year == 2011 & month > 6
replace pres = 35 if year == 2012 & month <= 6
replace pres = 36 if year == 2012 & month > 6
replace pres = 37 if year == 2013 & month <= 6
replace pres = 38 if year == 2013 & month > 6
replace pres = 39 if year == 2014 & month <= 6
replace pres = 40 if year == 2014 & month > 6
label var pres "Presidency"

* Define labels for presidency variable
label def presl /*
	*/ 1 "FR-1-95" /*
	*/ 2 "ES-2-95" /*
	*/ 3 "IT-1-96" /*
	*/ 4 "IE-2-96" /*
	*/ 5 "NL-1-97" /*
	*/ 6 "LU-2-97" /*
	*/ 7 "UK-1-98" /*
	*/ 8 "AT-2-98" /*
	*/ 9 "DE-1-99" /*
	*/ 10 "FI-2-99" /*
	*/ 11 "PT-1-00" /*
	*/ 12 "FR-2-00" /*
	*/ 13 "SE-1-01" /*
	*/ 14 "BE-2-01" /*
	*/ 15 "ES-1-02" /*
	*/ 16 "DK-2-02" /*
	*/ 17 "EL-1-03" /*
	*/ 18 "IT-2-03" /*
	*/ 19 "IE-1-04" /*
	*/ 20 "NL-2-04" /*
	*/ 21 "LU-1-05" /*
	*/ 22 "UK-2-05" /*
	*/ 23 "AT-1-06" /*
	*/ 24 "FI-2-06" /*
	*/ 25 "DE-1-07" /*
	*/ 26 "PT-2-07" /*	
	*/ 27 "SI-1-08" /*
	*/ 28 "FR-2-08" /*
	*/ 29 "CZ-1-09" /*
	*/ 30 "SE-2-09" /*
	*/ 31 "ES-1-10" /*
	*/ 32 "BE-2-10" /*
	*/ 33 "HU-1-11" /*
	*/ 34 "PL-2-11" /*
	*/ 35 "DK-1-12" /*
	*/ 36 "CY-2-12" /*
	*/ 37 "IE-1-13" /*
	*/ 38 "LT-2-13" /*
	*/ 39 "EL-1-14" /*
	*/ 40 "IT-2-14", modify	
label val pres presl
tab pres, m

* Sort and order data
sort date
order date slot dur cname cnum am pm ev year month week day semester pres

* Save meeting level dataset
compress
des, s
save "Output data\wpar-management03-coreper01-individual.dta", replace


**********************************
* Generate semester-level datasets
**********************************

* Collapse to semester data
collapse (sum) dur, by(cname year semester)
label var dur "Number of sessions"
des, s

* Generate semester of year variable
generate dates = yh(year, semester)
label var dates "Semester of year"
format dates %thh-CCYY

* Sort and order data
sort dates
order year dates semester dur cname

* Save dataset
compress
des, s
save "Output data\wpar-management03-coreper02-aggregate.dta", replace

* Close log
log close
exit
